package com.lambkit.db.sql;

import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson.JSONObject;
import com.jfinal.plugin.activerecord.DbPro;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.SqlPara;
import com.jfinal.plugin.activerecord.dialect.Dialect;
import com.jfinal.plugin.activerecord.dialect.MysqlDialect;
import com.lambkit.core.Lambkit;
import com.lambkit.core.http.IHttpContext;
import com.lambkit.db.meta.ColumnMeta;
import com.lambkit.db.meta.MetaKit;
import com.lambkit.db.meta.TableMeta;
import com.lambkit.db.mgr.MgrConstants;
import com.lambkit.db.mgr.MgrTable;
import com.lambkit.db.mgr.MgrdbService;
import com.lambkit.plugin.activerecord.dialect.LambkitDialect;
import com.lambkit.util.SqlKit;

import javax.servlet.http.HttpServletRequest;
import java.util.List;

public class ExampleBuilder {
	
	private Example example;
	private LambkitDialect dialect;

	public ExampleBuilder(String tableName, LambkitDialect dialect) {
		setDialect(dialect);
		setExample(Example.create(tableName));
	}
	
	public static ExampleBuilder by(JSONObject json) {
		String tableName = json.getString("table");
		if(StrUtil.isNotBlank(tableName)) {
//			if(tableName.contains(":")) {
//				String[] tbs = tableName.split(":");
//				configName = tbs[0];
//				tableName = tbs[1];
//			}
			String database = null;
			if (json.containsKey("database")) {
				database = json.getString("database");
			}

			MgrdbService service = Lambkit.context().getBean(MgrdbService.class);
			MgrTable tbc = service !=null ? service.createTable(tableName, MgrConstants.NONE, null) : null;
			if(tbc!=null) {
				ExampleBuilder builder = new ExampleBuilder(tbc.getName(), (LambkitDialect) tbc.dialect());
				ExampleJsonBuilder jsonBuilder = new ExampleJsonBuilder();
				builder.setExample(jsonBuilder.build(json, tbc));
				return builder;
			} else {
				TableMeta meta = MetaKit.createTable(database, tableName, "");
				if(meta!=null) {
					String dialectName = meta.getDialect();
					Dialect dialect1 = StrUtil.isBlank(dialectName) ? new MysqlDialect() : Lambkit.get(dialectName);
					ExampleBuilder builder = new ExampleBuilder(tableName, (LambkitDialect) dialect1);
					ExampleJsonBuilder jsonBuilder = new ExampleJsonBuilder();
					builder.setExample(jsonBuilder.build(json, meta));
					return builder;
				}
			}
		}
		return null;
	}
	
	public static ExampleBuilder by(MgrTable tbc, JSONObject json) {
		if(tbc!=null) {
			ExampleBuilder builder = new ExampleBuilder(tbc.getName(), (LambkitDialect) tbc.dialect());
			ExampleJsonBuilder jsonBuilder = new ExampleJsonBuilder();
			builder.setExample(jsonBuilder.build(json, tbc));
			return builder;
		}
		return null;
	}


	public static ExampleBuilder by(TableMeta meta, JSONObject json) {
		if(meta!=null) {
			String dialectName = meta.getDialect();
			Dialect dialect1 = StrUtil.isBlank(dialectName) ? new MysqlDialect() : Lambkit.get(dialectName);
			LambkitDialect lambkitDialect = (LambkitDialect) dialect1;
			ExampleBuilder builder = new ExampleBuilder(meta.getName(), lambkitDialect);
			ExampleJsonBuilder jsonBuilder = new ExampleJsonBuilder();
			builder.setExample(jsonBuilder.build(json, meta));
			return builder;
		}
		return null;
	}
	
	public static ExampleBuilder by(TableMeta tbc, IHttpContext controller) {
		if(tbc==null) {
			return null;
		}
		String dialectName = tbc.getDialect();
		Dialect dialect1 = StrUtil.isBlank(dialectName) ? new MysqlDialect() : Lambkit.get(dialectName);
		ExampleBuilder builder = new ExampleBuilder(tbc.getName(), (LambkitDialect) dialect1);
		builder.getExample().setAlias(tbc.getAttrName());
		List<ColumnMeta> flds = tbc.getColumnMetas();
		String nameStyle = controller.getPara("nameStyle");
		boolean isModel = "model".equalsIgnoreCase(nameStyle) ? true : false;
		for(int i=0; i<flds.size(); i++) {
			ColumnMeta fld = flds.get(i);
			String fldName = fld.getName();
			String paraName = isModel ? StrUtil.toCamelCase(fldName) : fldName;
			builder.column(fldName, getParaTrans(controller, paraName), fld.getJavaType(), true);
		}
		return builder;
	}

	public static ExampleBuilder by(MgrTable tbc, IHttpContext controller, String prefix) {
		if(tbc==null) {
			return null;
		}
		return by(tbc.getMeta(), controller, prefix);
	}

	public static ExampleBuilder by(TableMeta tbc, IHttpContext controller, String prefix) {
		if(tbc==null) {
			return null;
		}
		String dialectName = tbc.getDialect();
		Dialect dialect1 = StrUtil.isBlank(dialectName) ? new MysqlDialect() : Lambkit.get(dialectName);
		ExampleBuilder builder = new ExampleBuilder(tbc.getName(), (LambkitDialect) dialect1);
		String alias = tbc.getAttrName();
		if(StrUtil.isNotBlank(prefix)) {
			alias = prefix.endsWith(".") ? prefix.substring(0, prefix.length()-1) : prefix;
		}
		builder.getExample().setAlias(alias);
		List<ColumnMeta> flds = tbc.getColumnMetas();
		String nameStyle = controller.getPara("nameStyle");
		boolean isModel = "model".equalsIgnoreCase(nameStyle) ? true : false;
		for(int i=0; i<flds.size(); i++) {
			ColumnMeta fld = flds.get(i);
			String fldName = fld.getName();
			String paraName = isModel ? StrUtil.toCamelCase(fldName) : fldName;
			if(StrUtil.isNotBlank(prefix)) {
				prefix = prefix.endsWith(".") ? prefix : prefix + ".";
				paraName = prefix + paraName;
			}
			builder.column(fldName, getParaTrans(controller, paraName), fld.getJavaType(), true);
		}
		return builder;
	}
	
	private static String getParaTrans(IHttpContext controller, String name) {
		String param  = controller.getPara(name);
		if(param!=null && param.trim().length() > 0) {
			return SqlKit.transactSQLInjection(param);
		}
		return null;
	}

	public static ExampleBuilder by(MgrTable tbc, HttpServletRequest request) {
		if(tbc==null) {
			return null;
		}
		return by(tbc.getMeta(), request);
	}

	public static ExampleBuilder by(TableMeta meta, HttpServletRequest request) {
		if(meta==null) {
			return null;
		}
		String dialectName = meta.getDialect();
		Dialect dialect1 = StrUtil.isBlank(dialectName) ? new MysqlDialect() : Lambkit.get(dialectName);
		ExampleBuilder builder = new ExampleBuilder(meta.getName(), (LambkitDialect) dialect1);
		List<ColumnMeta> flds = meta.getColumnMetas();
		String nameStyle = request.getParameter("nameStyle");
		boolean isModel = "model".equalsIgnoreCase(nameStyle) ? true : false;
		for(int i=0; i<flds.size(); i++) {
			ColumnMeta fld = flds.get(i);
			String fldName = fld.getName();
			String paraName = isModel ? StrUtil.toCamelCase(fldName) : fldName;
			builder.column(fldName, getParaTrans(request, paraName), fld.getJavaType(), true);
		}
		return builder;
	}

	public static ExampleBuilder by(MgrTable tbc, HttpServletRequest request, String prefix) {
		if(tbc==null) {
			return null;
		}
		return by(tbc.getMeta(), request, prefix);
	}

	public static ExampleBuilder by(TableMeta meta, HttpServletRequest request, String prefix) {
		if(meta==null) {
			return null;
		}
		String dialectName = meta.getDialect();
		Dialect dialect1 = StrUtil.isBlank(dialectName) ? new MysqlDialect() : Lambkit.get(dialectName);
		ExampleBuilder builder = new ExampleBuilder(meta.getName(), (LambkitDialect) dialect1);
		String alias = meta.getAttrName();
		if(StrUtil.isNotBlank(prefix)) {
			alias = prefix.endsWith(".") ? prefix.substring(0, prefix.length()-1) : prefix;
		}
		builder.getExample().setAlias(alias);
		List<ColumnMeta> flds = meta.getColumnMetas();
		String nameStyle = request.getParameter("nameStyle");
		boolean isModel = "model".equalsIgnoreCase(nameStyle) ? true : false;
		for(int i=0; i<flds.size(); i++) {
			ColumnMeta fld = flds.get(i);
			String fldName = fld.getName();
			String paraName = isModel ? StrUtil.toCamelCase(fldName) : fldName;
			if(StrUtil.isNotBlank(prefix)) {
				prefix = prefix.endsWith(".") ? prefix : prefix + ".";
				paraName = prefix + paraName;
			}
			builder.column(fldName, getParaTrans(request, paraName), fld.getJavaType(), true);
		}
		return builder;
	}

	private static String getParaTrans(HttpServletRequest request, String name) {
		String param  = request.getParameter(name);
		if(param!=null && param.trim().length() > 0) {
			return SqlKit.transactSQLInjection(param);
		}
		return null;
	}

    public Record findFirst(DbPro db) {
    	 SqlPara sqlPara = getDialect().forFindByExample(example, null);
         return db.findFirst(sqlPara);
    }
    
    public List<Record> find(DbPro db) {
    	SqlPara sqlPara = getDialect().forFindByExample(example, null);
    	//System.out.println("SQL: " + sqlPara.getSql());
    	//System.out.println("para size: " + sqlPara.getPara().length);
        return db.find(sqlPara);
    }
    
    public List<Record> find(DbPro db, Integer count) {
    	SqlPara sqlPara = getDialect().forFindByExample(example, count);
    	//System.out.println("SQL: " + sqlPara.getSql());
    	//System.out.println("para size: " + sqlPara.getPara().length);
        return db.find(sqlPara);
    }
    
    public Page<Record> paginate(DbPro db, int pageNumber, int pageSize) {
    	SqlPara sqlPara = getDialect().forPaginateByExample(example);
        return db.paginate(pageNumber, pageSize, sqlPara);
    }
    
    public SqlPara findSqlPara() {
    	return getDialect().forFindByExample(example, null);
    }

    public SqlPara deleteSqlPara() {
    	return getDialect().forDeleteByExample(example);
    }

    public SqlPara updateSqlPara(Record record) {
    	return getDialect().forUpdateByExample(record, example);
    }
    
    public SqlPara findSqlPara(Integer count) {
    	return getDialect().forFindByExample(example, count);
    }
    
    public SqlPara paginateSqlPara() {
    	return getDialect().forPaginateByExample(example);
    }
    
	public ExampleBuilder orderBy(String orderby) {
		String oby = example.getOrderBy();
		if(StrUtil.isBlank(oby)) {
			oby = orderby;
		} else {
			oby += ", " + orderby;
		}
		example.setOrderBy(oby);
		return this;
	}
	
	public ExampleBuilder groupBy(String groupby) {
		GroupBy gby = example.getGroupBy();
		if(gby==null) {
			gby = new GroupBy();
		}
		if(StrUtil.isBlank(gby.getName())) {
			gby.setName(groupby);
		} else {
			String name = gby.getName();
			name += ", " + gby;
			gby.setName(name);
		}
		example.setGroupBy(gby);
		return this;
	}
	
	public ExampleBuilder select(String selectItem) {
    	String item = example.getLoadColumns();
		if(StrUtil.isBlank(item) || "*".equals(item)) {
			item = selectItem;
		} else {
			item += ", " + selectItem;
		}
		example.setLoadColumns(item);
		return this;
    }
	
	/**
	 * 加入select语句内容
	 * @param tbc
	 * @param alias
	 * @return
	 */
	public ExampleBuilder select(MgrTable tbc, String alias) {
		String select = example.getLoadColumns();
		if("*".equals(select)) {
			select = tbc.getSelectNamesOfView(alias);
		} else {
			select += ", " + tbc.getSelectNamesOfView(alias);
		}
		example.setLoadColumns(select);
		return this;
	}
	
	public Example example() {
		return example;
	}
	
//	public Columns and() {
//		return example.and();
//	}
//	
//	public Columns or() {
//		return example.or();
//	}
	
	public Columns columns() {
		return example.columns();
	}
	
	/**
	 * 加入where条件
	 * @param field
	 * @param value
	 * @param type
	 * @return
	 */
	public ExampleBuilder column(String field, String value, String type, boolean javaType) {
		example.columns().filter(field, value, type, javaType);
		return this;
	}

	public Example getExample() {
		return example;
	}

	public void setExample(Example example) {
		this.example = example;
	}

	public LambkitDialect getDialect() {
		return dialect;
	}

	public void setDialect(LambkitDialect dialect) {
		this.dialect = dialect;
	}
}
